from flask import Blueprint, render_template, request
from datetime import datetime
from database import get_db
from flask import make_response
import pandas as pd
from io import BytesIO
from reportlab.lib import colors
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

report_fuel_consumption_bp = Blueprint('report_fuel_consumption', __name__, url_prefix='/report/fuel-consumption')

@report_fuel_consumption_bp.route('/')
def index():
    # 固定默认值：2025年和珠海基地
    selected_year = 2025
    organization_boundary = "珠海基地"
    # 传递固定参数调用报告数据函数
    report_data = get_report_data(selected_year, organization_boundary)
    # 渲染模板时传递默认值
    return render_template('report_fuel_consumption.html', 
                         report_data=report_data,
                         selected_year=selected_year,
                         selected_organization=organization_boundary)


def get_report_data(year=2025, organization_boundary=None):
    db = get_db()

    # 构建查询条件
    query_params = [year]
    where_clauses = ['ci.belong_year = ?']
    
    # 添加组织边界筛选条件
    if organization_boundary:
        where_clauses.append('ci.organization_boundary = ?')
        query_params.append(organization_boundary)
    
    # 查询汽油消耗数据（活动数据总和与排放值）
    gasoline_sql = f'''
        SELECT SUM(ies.activity_data) as gasoline_activity
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        WHERE {' AND '.join(where_clauses)}
        AND cf.name LIKE '%汽油%' 
    '''
  
    gasoline_result = db.execute(gasoline_sql, query_params).fetchone()
    
    db.execute(
        "UPDATE report_fuel_consumption SET consumption = ? WHERE year = ? AND fuel_type LIKE '%汽油%'",
        (
            gasoline_result['gasoline_activity'],
            year
        )
    )
    db.commit()

    diesel_sql = f'''
        SELECT SUM(ies.activity_data) as diesel_activity
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        WHERE {' AND '.join(where_clauses)}
        AND cf.name LIKE '%柴油%' 
    '''
  
    diesel_result = db.execute(diesel_sql, query_params).fetchone()
    
    db.execute(
        "UPDATE report_fuel_consumption SET consumption = ? WHERE year = ? AND fuel_type LIKE '%柴油%'",
        (
            diesel_result['diesel_activity'],
            year
        )
    )
    db.commit()

    liquefaction_sql = f'''
        SELECT SUM(ies.activity_data) as liquefaction_activity
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        WHERE {' AND '.join(where_clauses)}
        AND cf.name LIKE '%液化石油气%' 
    '''
  
    liquefaction_result = db.execute(liquefaction_sql, query_params).fetchone()
    
    db.execute(
        "UPDATE report_fuel_consumption SET consumption = ? WHERE year = ? AND fuel_type LIKE '%液化石油气%'",
        (
            liquefaction_result['liquefaction_activity'],
            year
        )
    )
    db.commit()

    natural_gas_sql = f'''
        SELECT SUM(ies.activity_data) as natural_gas_activity
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        WHERE {' AND '.join(where_clauses)}
        AND cf.name LIKE '%天然气%' 
    '''
  
    natural_gas_result = db.execute(natural_gas_sql, query_params).fetchone()
    
    db.execute(
        "UPDATE report_fuel_consumption SET consumption = ? WHERE year = ? AND fuel_type LIKE '%天然气%'",
        (
            natural_gas_result['natural_gas_activity'],
            year
        )
    )
    db.commit()


    gasoline_sqls = f'''
        SELECT fuel_type,
            consumption,
            low_calorific_value,
            calorific_source,
            carbon_content,
            oxidation_rate,
            oxidation_source,
            emission_factor
        FROM report_fuel_consumption 
        WHERE year = {year}
    '''
    gasoline_results = db.execute(gasoline_sqls).fetchall()
    return [{
        'fuel_type': row['fuel_type'],
        'consumption': row['consumption'] or 0,
        'low_calorific_value': row['low_calorific_value'] or 0,
        'calorific_source': row['calorific_source'] or '',
        'carbon_content': row['carbon_content'] or 0,
        'oxidation_rate': row['oxidation_rate'] or 0,
        'oxidation_source': row['oxidation_source'] or '',
        'emission_factor': row['emission_factor'] or 0
    } for row in gasoline_results]

@report_fuel_consumption_bp.route('/export/<format>')
def export(format):
    report_data = get_report_data()
    df = pd.DataFrame(report_data)
    
    if format == 'excel':
        output = BytesIO()
        writer = pd.ExcelWriter(output, engine='xlsxwriter')
        df.to_excel(writer, sheet_name='燃料消耗报表', index=False)
        writer.close()
        output.seek(0)
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        response.headers['Content-Disposition'] = 'attachment; filename=fuel_consumption_report.xlsx'
        return response
    
    elif format == 'pdf':
        from reportlab.pdfgen import canvas
        from reportlab.lib.pagesizes import letter
        from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph
        from reportlab.lib.styles import getSampleStyleSheet
        
        pdfmetrics.registerFont(TTFont('SimSun', 'simsun.ttc'))
        
        output = BytesIO()
        doc = SimpleDocTemplate(output, pagesize=letter)
        
        data = [
            ['燃料品类', '消费量(t)', '低位发热量GJ/t或GJ/10⁴m³数据', '发热量数据来源', 
             '单位热值含碳量10⁻³tC/GJ', '碳氧化率%', '碳氧化率数据来源', 'CO₂排放因子tCO₂/GJ']
        ]
        for item in report_data:
            data.append([
                item.get('fuel_type', ''),
                item.get('consumption', ''),
                item.get('low_calorific_value', ''),
                item.get('calorific_source', ''),
                item.get('carbon_content', ''),
                item.get('oxidation_rate', ''),
                item.get('oxidation_source', ''),
                item.get('emission_factor', '')
            ])
        
        styles = getSampleStyleSheet()
        styles['Title'].fontName = 'SimSun'
        title = Paragraph('燃料消耗报表', styles['Title'])
        
        table = Table(data)
        table.setStyle(TableStyle([
            ('BACKGROUND', (0,0), (-1,0), colors.grey),
            ('TEXTCOLOR', (0,0), (-1,0), colors.whitesmoke),
            ('ALIGN', (0,0), (-1,-1), 'CENTER'),
            ('FONTNAME', (0,0), (-1,-1), 'SimSun'),
            ('FONTSIZE', (0,0), (-1,0), 10),
            ('BOTTOMPADDING', (0,0), (-1,0), 12),
            ('BACKGROUND', (0,1), (-1,-1), colors.white),
            ('GRID', (0,0), (-1,-1), 1, colors.black)
        ]))
        
        elements = [title, table]
        doc.build(elements)
        
        output.seek(0)
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/pdf'
        response.headers['Content-Disposition'] = 'attachment; filename=fuel_consumption_report.pdf'
        return response
    
    elif format == 'word':
        from docx import Document
        from docx.shared import Inches, Pt
        from docx.enum.text import WD_ALIGN_PARAGRAPH
        
        output = BytesIO()
        doc = Document()
        
        title = doc.add_paragraph('燃料消耗报表')
        title.alignment = WD_ALIGN_PARAGRAPH.CENTER
        title.runs[0].font.size = Pt(16)
        title.runs[0].bold = True
        title.runs[0].font.name = 'Microsoft YaHei'
        
        table = doc.add_table(rows=1, cols=8)
        table.style = 'Table Grid'
        
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = '燃料品类'
        hdr_cells[1].text = '消费量(t)'
        hdr_cells[2].text = '低位发热量GJ/t或GJ/10⁴m³数据'
        hdr_cells[3].text = '发热量数据来源'
        hdr_cells[4].text = '单位热值含碳量10⁻³tC/GJ'
        hdr_cells[5].text = '碳氧化率%'
        hdr_cells[6].text = '碳氧化率数据来源'
        hdr_cells[7].text = 'CO₂排放因子tCO₂/GJ'
        
        for item in report_data:
            row = table.add_row().cells
            row[0].text = str(item.get('fuel_type', ''))
            row[1].text = str(item.get('consumption', ''))
            row[2].text = str(item.get('low_calorific_value', ''))
            row[3].text = str(item.get('calorific_source', ''))
            row[4].text = str(item.get('carbon_content', ''))
            row[5].text = str(item.get('oxidation_rate', ''))
            row[6].text = str(item.get('oxidation_source', ''))
            row[7].text = str(item.get('emission_factor', ''))
        
        doc.save(output)
        output.seek(0)
        
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
        response.headers['Content-Disposition'] = 'attachment; filename=fuel_consumption_report.docx'
        return response
